/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.tables.daos.MtpkgMessageQueueDao;
import com.elcom.emocbc.dbmapping.tables.pojos.MtpkgMessageQueue;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class MtpkgMessageQueuePersistance extends MtpkgMessageQueueDao {

    public MtpkgMessageQueuePersistance() throws SQLException {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }

    public List<MtpkgMessageQueue> fetchByMsisdnInTimeRange(String msisdn, Date startDate, Date endDate) throws SQLException {
        ResultSetHandler<List<MtpkgMessageQueue>> resultSetHandler = new ResultSetHandler<List<MtpkgMessageQueue>>() {
            @Override
            public List<MtpkgMessageQueue> handle(ResultSet rs) throws SQLException {
                List<MtpkgMessageQueue> result = new ArrayList<MtpkgMessageQueue>();
                while (rs.next()) {
                    MtpkgMessageQueue message = new MtpkgMessageQueue();
                    int i = 1;
                    message.setMsisdn(rs.getString(i++));
                    message.setId(new BigInteger(String.valueOf(rs.getInt(i++))));
                    message.setStatus(rs.getByte(i++));
                    message.setSmsType(rs.getString(i++));
                    message.setSendTime(rs.getTimestamp(i++));
                    message.setSentTime(rs.getTimestamp(i++));
                    message.setContent(rs.getString(i++));
                    message.setPriority(rs.getByte(i++));
                    message.setErrorCode(rs.getLong(i++));
                    message.setMessageId(rs.getString(i++));
                    message.setErrorDetail(rs.getString(i++));
                    result.add(message);
                }
                return result;
            }
        };
        Connection con = this.configuration().connectionProvider().acquire();

        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * "
                + "FROM mtpkg_message_queue m "
                + "WHERE m.sent_time BETWEEN ? AND ? "
                + "AND m.msisdn = ? ";
        try {
            return qr.query(con, sql, resultSetHandler, new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()), msisdn);
        } finally {
            this.configuration().connectionProvider().release(con);
        }
    }
}
